<?php
class Dashboards extends Model{
	
	function getContact($id){
        $sql ="SELECT *,at.pic FROM activity_transaction at ";
        $sql.="LEFT JOIN activity a ON a.id=at.activity_id ";
        $sql.="LEFT JOIN customers c  ON c.id=at.customer_id ";
        //if(($this->session->userdata('user_pos')== $this->config->item('ta')) && ($this->session->userdata('user_group')== $this->config->item('user')) ): 
		if( ($this->session->userdata('user_pos')== $this->config->item('ta')) || ($this->session->userdata('user_pos')== $this->config->item('se')) || ($this->session->userdata('user_pos')== $this->config->item('ae')) ) 	
			$sql.=" WHERE at.pic='".$this->session->userdata('user_id')."'";
        
		//elseif(($this->session->userdata('user_pos')== $this->config->item('se')) && ($this->session->userdata('user_group')== $this->config->item('user')) ):
            //$sql.=" WHERE at.pic='".$this->session->userdata('user_ta')."'";     
        //endif;
        $sql.=" ORDER BY at.activity_date DESC";
        $sql.= " LIMIT ".$id;
        $query=$this->db->query($sql);
		return $query->result_array();
	}
	
	function getCustomers($limit='',$type='',$div=''){
        $sql =" SELECT * FROM customers ";
		$sql.=" WHERE customer_name<>'' ";
		if(($this->session->userdata('user_pos')== $this->config->item('se'))):
			if($this->session->userdata('user_group')== $this->config->item('user_dna')):
				$division='DNA';
			else:
				$division='IN HOUSE';
			endif;
			$sql.=" AND se_approve = 0 AND spv_approve=0 AND division ='".$division."' AND se_name='".$this->session->userdata('user_id')."'";
		elseif((($this->session->userdata('user_pos')== $this->config->item('spv')) && ($this->session->userdata('user_group')== $this->config->item('user'))  ) || ($this->session->userdata('user_pos')== $this->config->item('mgr'))):
			$sql.=" AND se_approve = 1 AND spv_approve=0 ";
		elseif((($this->session->userdata('user_pos')== $this->config->item('spv')) && ($this->session->userdata('user_group')== $this->config->item('user_dna'))  ) || ($this->session->userdata('user_pos')== $this->config->item('mgr'))):
			$sql.=" AND se_approve = 1 AND spv_approve=1 AND rev3='' ";	
		endif;
		
		if($type){
			$sql.=" AND status_id=".$type;
		}
		
		if($div){
			$sql.=" AND division='".$div."' ";
		}
		
		
        $sql.=" ORDER BY date_stamp ASC ";
        
		if(($this->session->userdata('user_pos')== $this->config->item('se'))):
			$sql.=" ,RAND() ";
			$user=$this->getSE();
			$cust=$this->getSECustomer();
			$limit=number_format($cust/$user,0);
			
		else:
			$limit=$limit;
        endif;
		
		$sql.=" LIMIT ".$limit;
		$query=$this->db->query($sql);
		//echo $this->db->last_query();
		return $query->result_array();
	}
	
	function getSE(){
		$sql ="SELECT * FROM users ";
        $sql.=" WHERE pos_id=".$this->config->item('se');
        $sql.=" AND sales=1 ";
        $sql.=" AND enabled=1 ";
		if($this->session->userdata('user_group')==$this->config->item('user_dna')):
			$sql.=" AND group_id=".$this->config->item('user_dna');
		else:
			$sql.=" AND group_id=".$this->config->item('user');
		endif;
		$query=$this->db->query($sql);
		return $query->num_rows();
	}
	
	function getSECustomer(){
		$sql =" SELECT * FROM customers ";
		if(($this->session->userdata('user_pos')== $this->config->item('se'))):
			if($this->session->userdata('user_group')== $this->config->item('user_dna')):
				$division='DNA';
			else:
				$division='IN HOUSE';
			endif;
			$sql.=" WHERE se_approve = 0 AND spv_approve=0 AND division ='".$division."' AND status_id=".$this->config->item('prospect');
		endif;
		$query=$this->db->query($sql);
		return $query->num_rows();
	}
    
    function getUSers(){
		$sql ="SELECT * FROM users ";
        $sql.=" WHERE pos_id=".$this->config->item('ta');
        $sql.=" AND sales=1 ";
        $sql.=" AND enabled=1 ";
        $sql.=" AND group_id=".$this->config->item('user');
		$query=$this->db->query($sql);
		return $query->result_array();
	}
    
    function getUserID($pos,$group){
        $sql ="SELECT * FROM users ";
        $sql.=" WHERE pos_id=".$pos;
        $sql.=" AND sales=1 ";
        $sql.=" AND enabled=1 ";
        $sql.=" AND group_id=".$group;
		$query=$this->db->query($sql);
		return $query->result_array();
    }
	
	function getAllUSers(){
		$sql =" SELECT * FROM users u ";
        $sql.=" LEFT JOIN user_position up  ON up.id=u.pos_id ";
		if($this->session->userdata('user_group')==$this->config->item('user_dna')):
			$sql.=" WHERE  pos_id=".$this->config->item('ae');
			$sql.=" AND u.group_id=".$this->config->item('user_dna')."";
		else:
			$sql.=" WHERE pos_id=".$this->config->item('ta');
			$sql.=" AND u.group_id=".$this->config->item('user')."";
		endif;
        $sql.=" AND u.sales=1 ";
        $sql.=" AND u.enabled=1 ";
		$sql.=" ORDER BY ABS(u.pos_id) ASC ";
		$query=$this->db->query($sql);
		return $query->result_array();
	}
    
    function getType(){
		$sql ="SELECT * FROM activity Order by id ASC";
		$query=$this->db->query($sql);
		return $query->result_array();
	}
    
    function getAllThisTransaction($date){
        $sql =" SELECT * ,at.id,at.pic,WEEK(at.activity_date) as week,MONTHNAME(at.activity_date) as month,YEAR(at.activity_date) as year ";
        $sql.=" FROM activity_transaction at  ";
        $sql.=" LEFT JOIN customers c  ON c.id=at.customer_id ";
		$sql.=" LEFT JOIN users u  ON u.username=at.pic ";
        $sql.=" WHERE at.activity_date <> '0000-00-00' ";
        $sql.=" AND  WEEK(at.activity_date) ='".$date."'";
         if( ($this->session->userdata('user_group')== $this->config->item('admin')) || ($this->session->userdata('user_group')== $this->config->item('admin_dna')) || ($this->session->userdata('user_group')== $this->config->item('prod')) ):
			if($this->session->userdata('user_group')== $this->config->item('admin_dna')):
				$sql.=" AND u.group_id=".$this->config->item('user_dna');
			else:
			    $sql.=" AND u.group_id=".$this->config->item('user');
			endif;	
		 else: 
            if(($this->session->userdata('user_pos')== $this->config->item('ta'))): 
                $sql.=" AND at.pic='".$this->session->userdata('user_id')."'";
            elseif(($this->session->userdata('user_pos')== $this->config->item('se'))):
                $sql.=" AND at.pic='".$this->session->userdata('user_ta')."'";     
            endif;
         endif;
        $query=$this->db->query($sql);
		return $query->result_array();
    }
	
	function getWeekActivity($user,$week){
		 $sql =" SELECT * ,at.pic,c.customer_name";
		 $sql.=" FROM activity_transaction at ";	
		 $sql.=" LEFT JOIN customers c  ON c.id=at.customer_id ";
		 $sql.=" WHERE at.pic='".$user."'";
		 $sql.=" AND (WEEK(at.activity_date, 5) - WEEK(DATE_SUB(at.activity_date, INTERVAL DAYOFMONTH(at.activity_date) - 1 DAY), 5) + 0)= '".$week."'";
		 $query=$this->db->query($sql);
		 return $query->result_array();
	}
	
	function getWeekSalesFail($user,$week){
		 $sql =" SELECT * ,sf.user_stamp as pic,c.customer_name,p.product_name";
		 $sql.=" FROM sales_fails sf ";	
		 $sql.=" LEFT JOIN customers c  ON c.id=sf.cust_id ";
		 $sql.=" LEFT JOIN products p  ON p.id=sf.prod_id ";
		 $sql.=" WHERE sf.user_stamp='".$user."'";
		 $sql.=" AND (WEEK(sf.date_posting, 5) - WEEK(DATE_SUB(sf.date_posting, INTERVAL DAYOFMONTH(sf.date_posting) - 1 DAY), 5) + 0)= '".$week."'";
		 $query=$this->db->query($sql);
		 return $query->result_array();
	}
    
	function getWeekJob($user,$week){
        $sql ="SELECT *,j.id,j.pic,((p.invest + (p.invest * j.ppn / 100) - j.discount) ) as invest,jo_batch as batch FROM jobs j ";
		$sql.="LEFT JOIN products p ON p.id=j.product_id ";
		//$sql.="LEFT JOIN customers c ON c.id=j.customer_id ";
        $sql.=" WHERE j.pic='".$user."'";
		$sql.=" AND (WEEK(j.date_posting, 5) - WEEK(DATE_SUB(j.date_posting, INTERVAL DAYOFMONTH(j.date_posting) - 1 DAY), 5) + 0)= '".$week."'";
        $query=$this->db->query($sql);
		return $query->result_array();
    }
	
    function getJOMonth(){
        $sql =" SELECT u.username, ";
        $sql.="(SELECT SUM( ((p.invest + (p.invest * j.ppn / 100) - j.discount)) ) FROM jobs j JOIN customers c ON c.id=j.customer_id JOIN products p ON p.id=j.product_id WHERE j.pic=u.username AND MONTH(j.date_in)='".date('m')."') as total ";
        $sql.=" FROM users u ";
        $sql.=" WHERE pos_id=4 AND enabled=1 AND sales=1 ";
        $sql.=" AND group_id=".$this->config->item('user');
        $query=$this->db->query($sql);
		return $query->result_array();
    }
    
    function getJOTotal(){
        $sql =" SELECT SUM(p.invest) as total FROM jobs j JOIN products p ON p.id=j.product_id WHERE MONTH(j.date_in)='".date('m')."'";
        $query=$this->db->query($sql);
		return $query->row_array();
    }
	
	function getUpdateProspectTA($id){
		$value = array(
					'se_approve' => 1,
					'se_name'	 => $this->session->userdata('user_id') 	
		);
		$this->db->where('id',$id);
		$this->db->update('customers',$value);
	}
	
	function getUpdateExisting($id){
		$value = array(
					'spv_approve' => 1,
					'spv_name'	  => $this->session->userdata('user_id') 	
		);
		$this->db->where('id',$id);
		$this->db->update('customers',$value);
	}
	
	function getUpdateAll($div){
			$this->db->where('spv_approve',0);
			$this->db->like('division',$div);
			$Q=$this->db->get('customers');
			$cust=$Q->result_array();
			foreach($cust as $cust):
				if($cust['rev']<>''):
					$this->db->where('pos_id',$this->config->item('ae'));
					$this->db->order_by('RAND()');
					$Q=$this->db->get('users',1);
					$user=$Q->row_array();
					$value['rev3']=$user['username']; 
				elseif($cust['rev2']<>''):
					$this->db->where('group_id',$this->session->userdata('user_group'));
					if($this->session->userdata('user_group')==$this->config->item('user')):
						$this->db->where('pos_id',$this->config->item('ta'));
					else:
						$this->db->where('pos_id',$this->config->item('ae'));
					endif;
					$this->db->order_by('RAND()');
					$Q=$this->db->get('users',1);
					$user=$Q->row_array();
					$value['rev']=$user['username']; 	
				elseif($cust['rev3']<>''):
					$this->db->where('group_id',$this->session->userdata('user_group'));
					$this->db->where('pos_id',$this->config->item('ta'));
					$this->db->order_by('RAND()');
					$Q=$this->db->get('users',1);
					$user=$Q->row_array();
					$value['rev']=$user['username']; 
				endif;
				
				$value['spv_approve'] = 1;
				$value['spv_name']	  = $this->session->userdata('user_id');
				$this->db->where('id',$cust['id']);
				$this->db->update('customers',$value);
			endforeach;
	}
	
	function getUpdateProspectSE($id,$div=''){
		if($id):
			$this->db->where('id',$id);
			$Q=$this->db->get('customers',1);
			$data=$Q->row_array();
			if($data['rev']<>''):
				if($this->session->userdata('user_group')==$this->config->item('user_dna')):
					$this->db->where('pos_id',$this->config->item('ae'));
					$this->db->order_by('(SELECT COUNT(*) FROM customers WHERE customers.rev3=users.username) ASC');
					$Q=$this->db->get('users');
					$user=$Q->row_array();
					$value['rev3']=$user['username'];
					$value['customer_from'] = 'Manajemen';
				else:
					$this->db->where('pos_id',$this->config->item('ta'));
					$this->db->order_by('(SELECT COUNT(*) FROM customers WHERE customers.rev=users.username) ASC');
					$Q=$this->db->get('users');
					$user=$Q->row_array();
					$value['rev']=$user['username'];
				endif;	
			elseif($data['rev2']<>''):
				if($this->session->userdata('user_group')==$this->config->item('user_dna')):
					$this->db->where('pos_id',$this->config->item('ae'));
					$this->db->order_by('(SELECT COUNT(*) FROM customers WHERE customers.rev3=users.username) ASC');
					$Q=$this->db->get('users');
					$user=$Q->row_array();
					$value['rev3']=$user['username'];
					$value['customer_from'] = 'Manajemen';
				else:
					$this->db->where('pos_id',$this->config->item('ta'));
					$this->db->order_by('(SELECT COUNT(*) FROM customers WHERE customers.rev=users.username) ASC');
					$Q=$this->db->get('users',1);
					$user=$Q->row_array();
					$value['rev']=$user['username'];
				endif;	
			elseif($data['rev3']<>''):
				$this->db->where('group_id',$this->session->userdata('user_group'));
				$this->db->where('pos_id',$this->config->item('ta'));
				$this->db->order_by('(SELECT COUNT(*) FROM customers WHERE customers.rev3=users.username) ASC');
				$Q=$this->db->get('users',1);
				$user=$Q->row_array();
				$value['rev']=$user['username']; 
			endif;
		endif;
		
		$value['spv_approve'] = 1;
		$value['spv_name']	  = $this->session->userdata('user_id');
		$this->db->where('id',$id);
		$this->db->update('customers',$value);
	}
	
	function getDeleteCustomer($id){
		$this->db->where('id',$id);
		$this->db->delete('customers',$value);
	}
	
	function getProspectUser($id){
		$value = array(
					'se_approve' => 0,
					'se_name'	 => '' 	
		);
		$this->db->where('id',$id);
		$this->db->update('customers',$value);
	}
	
	function getRejectSpv($id){
		$value = array(
					'customer_status'	=> 4,
					'spv_approve' 		=> 1,
					'spv_name'	 		=> $this->session->userdata('user_id') 	
		);
		$this->db->where('id',$id);
		$this->db->update('customers',$value);
	}
    
    function getActivityPerPeriode($periode=''){
        $sql =" SELECT activity_date,MONTHNAME(activity_date) as monthly,(DATE_FORMAT(activity_date,'%d') DIV 7 + 1) as weeks,at.pic, ";
        if($periode=='Month'){
            $sql.=" (SELECT COUNT(*) FROM activity_transaction ax WHERE ax.activity_id=1 AND MONTH(ax.activity_date)=MONTH(at.activity_date) AND ax.pic=at.pic ) as calls, ";
            $sql.=" (SELECT COUNT(*) FROM activity_transaction ax WHERE ax.activity_id=2 AND MONTH(ax.activity_date)=MONTH(at.activity_date) AND ax.pic=at.pic ) as invalid, ";
            $sql.=" (SELECT COUNT(*) FROM activity_transaction ax WHERE ax.activity_id=3 AND MONTH(ax.activity_date)=MONTH(at.activity_date) AND ax.pic=at.pic) as presentation, ";
            $sql.=" (SELECT COUNT(*) FROM activity_transaction ax WHERE ax.activity_id=4 AND MONTH(ax.activity_date)=MONTH(at.activity_date) AND ax.pic=at.pic) as meeting, ";
            $sql.=" (SELECT COUNT(*) FROM activity_transaction ax WHERE ax.activity_id=6 AND MONTH(ax.activity_date)=MONTH(at.activity_date) AND ax.pic=at.pic) as fu ";
        }
        else
		{
            $sql.=" (SELECT COUNT(*) FROM activity_transaction ax WHERE ax.activity_id=1 AND ax.activity_date=at.activity_date AND ax.pic=at.pic ) as calls, ";
            $sql.=" (SELECT COUNT(*) FROM activity_transaction ax WHERE ax.activity_id=2 AND ax.activity_date=at.activity_date AND ax.pic=at.pic ) as invalid, ";
            $sql.=" (SELECT COUNT(*) FROM activity_transaction ax WHERE ax.activity_id=3 AND ax.activity_date=at.activity_date AND ax.pic=at.pic ) as presentation, ";
            $sql.=" (SELECT COUNT(*) FROM activity_transaction ax WHERE ax.activity_id=4 AND ax.activity_date=at.activity_date AND ax.pic=at.pic ) as meeting, ";
            $sql.=" (SELECT COUNT(*) FROM activity_transaction ax WHERE ax.activity_id=6 AND ax.activity_date=at.activity_date AND ax.pic=at.pic ) as fu ";
        }
        $sql.=" FROM ";
        $sql.=" activity_transaction at ";
        $sql.=" JOIN users u ON u.username=at.pic";
		
		if($this->session->userdata('user_pos')== $this->config->item('ta') ):
			$sql.=" WHERE u.pos_id=".$this->config->item('ta');
			$sql.=" AND MONTH(activity_date)='".date('m')."'";
		else:
			$sql.=" WHERE u.pos_id=".$this->config->item('se');
        endif;
		
		
		$sql.=" AND YEAR(activity_date)='".date('Y')."'";
        
        if($this->session->userdata('user_pos')==$this->config->item('se')):
            $sql.=" AND at.pic='".$this->session->userdata('user_id')."'";
		elseif($this->session->userdata('user_pos')==$this->config->item('ta')):
            $sql.=" AND at.pic='".$this->session->userdata('user_id')."'";	
        elseif($this->session->userdata('user_pos')==$this->config->item('spv')):
            $sql.=" AND u.group_id='".$this->session->userdata('user_group')."'";
        endif;
        
        if($periode=='Month'){
             if($this->session->userdata('user_pos')==$this->config->item('se')): 
                $sql.=" GROUP BY MONTH(at.activity_date) ";    
             elseif($this->session->userdata('user_pos')==$this->config->item('spv')):
                $sql.=" GROUP BY MONTH(at.activity_date),at.pic  ";   
             endif;    
        } else {
            if($this->session->userdata('user_pos')==$this->config->item('se')): 
                $sql.=" GROUP BY at.activity_date ";    
            elseif($this->session->userdata('user_pos')==$this->config->item('ta')): 
                $sql.=" GROUP BY (DATE_FORMAT(at.activity_date,'%d') DIV 7 + 1) ";
			elseif($this->session->userdata('user_pos')==$this->config->item('spv')):
                $sql.=" GROUP BY at.activity_date,at.pic  ";   
            endif;       
        }
        $sql.=" ORDER BY activity_date ";  
        $query=$this->db->query($sql);
		return $query->result_array();
    }
    
    function activity3Month($month01,$month02,$pic=''){
        $sql =" SELECT at.pic, ";
        $sql.=" (SELECT COUNT(*) FROM activity_transaction ax WHERE ax.activity_id=1 AND ax.pic=at.pic ) as calls, ";
        $sql.=" (SELECT COUNT(*) FROM activity_transaction ax WHERE ax.activity_id=2 AND ax.pic=at.pic ) as invalid, ";
        $sql.=" (SELECT COUNT(*) FROM activity_transaction ax WHERE ax.activity_id=3 AND ax.pic=at.pic ) as resentation, ";
        $sql.=" (SELECT COUNT(*) FROM activity_transaction ax WHERE ax.activity_id=4 AND ax.pic=at.pic ) as meeting, ";
        $sql.=" (SELECT COUNT(*) FROM activity_transaction ax WHERE ax.activity_id=6 AND ax.pic=at.pic ) as fu ";
        $sql.=" FROM ";
        $sql.=" activity_transaction at ";
        $sql.=" JOIN users u ON u.username=at.pic";
		
		if($this->session->userdata('user_pos')==$this->config->item('ta')):
			$sql.=" WHERE u.pos_id=".$this->config->item('ta');
		else:
			$sql.=" WHERE u.pos_id=".$this->config->item('se');
		endif;
		
        $sql.=" AND MONTH(activity_date)>='".$month01."'  AND MONTH(activity_date) <='".$month02."'";
        if($this->session->userdata('user_pos')!=$this->config->item('spv')):
            $sql.=" AND at.pic='".$this->session->userdata('user_id')."'";
        else:
            $sql.=" AND at.pic='".$pic."'";
        endif;
        $query=$this->db->query($sql);
		return $query->row_array();
    }
    
}    